package cn.vote.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.sun.org.apache.bcel.internal.generic.NEW;

import cn.itcast.jdbc.TxQueryRunner;
import cn.vote.domain.Voteinfo;
import cn.vote.domain.Voterresult;

public class VoteinfoDao {
	private QueryRunner qr = new TxQueryRunner();
	/**
	 * 根据类别查询出作品
	 * @param category
	 * @return
	 * @throws SQLException
	 */
	public List<Voteinfo> getWorksInfo(int category) throws SQLException{
		
		String sql = "SELECT DISTINCT worksid,works,author,imageurl FROM voteinfo WHERE category = ?";
		return qr.query(sql, new BeanListHandler<Voteinfo>(Voteinfo.class), category);
	}
	/**
	 * 插入voter投票结果
	 * @throws SQLException 
	 */
	public void insert(String worksid,String voter,int category) throws SQLException{
		String sql = "insert into voterresult values(?,?,?)";
		qr.update(sql, worksid,voter,category);
	}
	/**
	 * 通过类别和worksid查询出该作品的得票
	 * @throws SQLException 
	 */
	public int getPoll(String worksid,int category) throws SQLException{
		String sql = "select count(*) from voterresult where worksid=? and category=?";
		Number number = (Number)qr.query(sql, new ScalarHandler(),worksid,category);
		int poll = number.intValue();
		return poll;
	}
	/**
	 * 得到总票数
	 * @throws SQLException 
	 */
	public int getTotal() throws SQLException{
		String sql = "select count(*) from voterresult ";
		Number number = (Number)qr.query(sql, new ScalarHandler());
		int total = number.intValue();
		return total;
	}
	/**
	 * 通过类别和worksid查询出该作品的信息
	 * @throws SQLException 
	 */
	public Voterresult getInfo(String worksid,int category) throws SQLException{
		String sql = "select * from voterresult where worksid=? and category=?";
		return qr.query(sql, new BeanHandler<Voterresult>(Voterresult.class), worksid,category);
	}
	public Voteinfo getWorks(String worksid,int category) throws SQLException{
		String sql = "select * from voteinfo where worksid=? and category=?";
		return qr.query(sql, new BeanHandler<Voteinfo>(Voteinfo.class), worksid,category);
	}
	public Voterresult judge(String username) throws SQLException {
		String sql = "SELECT DISTINCT voter FROM voterresult WHERE voter=?";
		return qr.query(sql, new BeanHandler<Voterresult>(Voterresult.class), username);
	}
}
